"""
数据表格界面
"""

import time

import xlrd
from PyQt5.QtCore import QThread
from PyQt5.QtWidgets import *
from PyQt5 import QtCore
from PyQt5.QtWidgets import QHeaderView
from lib import db
import lib.XLSX as XLSX
import Simulation


class Update_table(QThread, Simulation.Simulation_cfg):
    """更新数据库类"""
    signal_write_msg = QtCore.pyqtSignal(list)
    signal_combox_msg = QtCore.pyqtSignal(list)
    signal_update_table_msg = QtCore.pyqtSignal(list)

    def __init__(self):
        super().__init__()
        self.db = db.database()

    def run(self):
        # 这一步主要是想让以后的表格刷新过程中，前两个表格不刷新
        try:
            self.Update(init=True)
        except:
            pass
        time.sleep(self.Flag_table_update_time)
        while True:
            try:
                self.Update(init=False)
            except:
                pass
            time.sleep(self.Flag_table_update_time)

    def Update(self, init=True):
        func = [self.db.Read_Road(), self.db.Read_Crossing(), self.db.Read_Bus(), self.db.Read_Request(),
                self.db.Read_Station(),
                self.db.Read_His_Bus()]
        for k in range(len(func)):
            mess = func[k]
            if not init and not k == 0 and not k == 1:
                self.signal_update_table_msg.emit([k, 'clear', 0])
                self.signal_update_table_msg.emit([k, 'set_col', len(mess)])
            elif init:
                self.signal_update_table_msg.emit([k, 'clear', 0])
                self.signal_update_table_msg.emit([k, 'set_col', len(mess)])
            if k == 0 and init:
                """Road"""
                for i in range(len(mess)):
                    for j in range(len(mess[i])):
                        addmess = str(mess[i][j])
                        self.signal_write_msg.emit([k, i, j, addmess])
            elif k == 1 and init:
                """Crossing"""
                combox_item = self.db.GET_all_Road_name()
                for i in range(len(mess)):
                    for j in range(1):
                        addmess = str(mess[i][j])
                        self.signal_write_msg.emit([k, i, j, addmess])
                    for j in range(1, 5):
                        self.signal_combox_msg.emit([k, i, j, combox_item, mess[i][j]])
                    for j in range(5, len(mess[i])):
                        addmess = str(mess[i][j])
                        self.signal_write_msg.emit([k, i, j, addmess])
            elif k == 2:
                """Bus"""
                combox_item_Road = self.db.GET_all_Road_id()
                for i in range(len(mess)):
                    for j in range(1):
                        addmess = str(mess[i][j])
                        self.signal_write_msg.emit([k, i, j, addmess])
                    for j in range(1, 2):
                        self.signal_combox_msg.emit([k, i, j, combox_item_Road, mess[i][j]])
                    for j in range(2, len(mess[i])):
                        addmess = str(mess[i][j])
                        self.signal_write_msg.emit([k, i, j, addmess])
            elif k == 3:
                """Request"""
                combox_item_Crossing = self.db.GET_all_Crossing_id()
                combox_item_Bus = self.db.GET_all_Bus_id()
                for i in range(len(mess)):
                    # for j in range(1):
                    #     self.signal_combox_msg.emit([k, i, j, combox_item_Crossing, mess[i][j]])
                    # for j in range(1, 2):
                    #     self.signal_combox_msg.emit([k, i, j, combox_item_Bus, mess[i][j]])
                    # for j in range(2, len(mess[i])):
                    #     addmess = str(mess[i][j])
                    #     self.signal_write_msg.emit([k, i, j, addmess])
                    for i in range(len(mess)):
                        for j in range(len(mess[i])):
                            addmess = str(mess[i][j])
                            self.signal_write_msg.emit([k, i, j, addmess])

            elif k == 4:
                """His_bus"""
                for i in range(len(mess)):
                    for j in range(len(mess[i])):
                        addmess = str(mess[i][j])
                        self.signal_write_msg.emit([k, i, j, addmess])
            elif k == 5:
                """His_bus"""
                for i in range(len(mess)):
                    for j in range(len(mess[i])):
                        addmess = str(mess[i][j])
                        self.signal_write_msg.emit([k, i, j, addmess])


class Form3(QWidget, Simulation.Simulation_cfg):

    def __init__(self, parent=None):
        super().__init__(parent)
        self.db = db.database()

        self.btnPress1 = QPushButton("初始化数据")
        self.btnPress2 = QPushButton("清空数据库")
        self.btnPress3 = QPushButton("加载数据")
        self.btnPress4 = QPushButton("修改数据")
        self.btnPress5 = QPushButton("保存数据.xlsx")
        self.btnPress6 = QPushButton("读取数据.xlsx")
        self.btnPress1.setFixedSize(170, 30)
        self.btnPress2.setFixedSize(170, 30)
        self.btnPress3.setFixedSize(170, 30)
        self.btnPress4.setFixedSize(170, 30)
        self.btnPress5.setFixedSize(170, 30)
        self.btnPress6.setFixedSize(170, 30)
        btn_qssStyle = ''' 
                            QPushButton{
                            text-align : center;
                            font-family:Microsoft Yahei;
                            font-size:11pt;
                            background-color : #F5F5F5;
                            border-color: gray;
                            border-width: 2px;
                            border-radius: 10px;
                            padding: 1px;
                            height : 14px;
                            border-style: outset;
                            }
                            QPushButton:pressed{
                            text-align : center;
                            font-family:Microsoft Yahei;
                            font-size:11pt;
                            background-color : #ADD8E6;
                            border-color: gray;
                            border-width: 2px;
                            border-radius: 10px;
                            padding: 1px;
                            height : 14px;
                            border-style: outset;
                            }
                        '''

        self.btnPress1.setStyleSheet(btn_qssStyle)
        self.btnPress2.setStyleSheet(btn_qssStyle)
        self.btnPress3.setStyleSheet(btn_qssStyle)
        self.btnPress4.setStyleSheet(btn_qssStyle)
        self.btnPress5.setStyleSheet(btn_qssStyle)
        self.btnPress6.setStyleSheet(btn_qssStyle)


        # 表格layout
        self.btn_layout = QHBoxLayout()
        self.btn_layout.addStretch()
        self.btn_layout.addWidget(self.btnPress1)
        self.btn_layout.addWidget(self.btnPress2)
        self.btn_layout.addWidget(self.btnPress3)
        self.btn_layout.addWidget(self.btnPress4)
        self.btn_layout.addWidget(self.btnPress5)
        self.btn_layout.addWidget(self.btnPress6)
        self.btn_layout.addStretch()

        titles1 = self.db.Get_Col_Name('road')
        self.table1 = QTableWidget(self)  # creat a empty table
        self.table1.setRowCount(9)  # set the row num, exc the header
        self.table1.setColumnCount(len(titles1))  # set the col num
        self.table1.setHorizontalHeaderLabels(titles1)  # set the header
        # self.table1.verticalHeader().setVisible(False)  # hide the verticalHeader / or not
        self.table1.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table1.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)  # set right click
        self.table1.customContextMenuRequested[QtCore.QPoint].connect(
            lambda pos: self.table_event(pos, self.table1))  # the right click event
        self.table1.hide()

        titles2 = self.db.Get_Col_Name('crossing')
        self.table2 = QTableWidget(self)  # creat a empty table
        self.table2.setRowCount(9)
        self.table2.setColumnCount(len(titles2))
        self.table2.setHorizontalHeaderLabels(titles2)
        # self.table2.verticalHeader().setVisible(False)  # hide the verticalHeader / or not
        self.table2.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table2.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
        self.table2.customContextMenuRequested[QtCore.QPoint].connect(
            lambda pos: self.table_event(pos, self.table2))  # the right click event
        self.table2.hide()

        titles3 = self.db.Get_Col_Name('bus')
        self.table3 = QTableWidget(self)  # creat a empty table
        self.table3.setRowCount(9)
        self.table3.setColumnCount(len(titles3))
        self.table3.setHorizontalHeaderLabels(titles3)
        # self.table3.verticalHeader().setVisible(False)  # hide the verticalHeader / or not
        self.table3.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table3.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
        self.table3.customContextMenuRequested[QtCore.QPoint].connect(
            lambda pos: self.table_event(pos, self.table3))  # the right click event
        self.table3.hide()

        titles4 = self.db.Get_Col_Name('request')
        self.table4 = QTableWidget(self)  # creat a empty table
        self.table4.setRowCount(9)
        self.table4.setColumnCount(len(titles4))
        self.table4.setHorizontalHeaderLabels(titles4)
        # self.table4.verticalHeader().setVisible(False)  # hide the verticalHeader / or not
        self.table4.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table4.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
        self.table4.customContextMenuRequested[QtCore.QPoint].connect(lambda pos: self.table_event(pos, self.table4))
        self.table4.hide()

        titles5 = self.db.Get_Col_Name('station')
        self.table5 = QTableWidget(self)  # creat a empty table
        self.table5.setRowCount(9)
        self.table5.setColumnCount(len(titles5))
        self.table5.setHorizontalHeaderLabels(titles5)
        # self.table5.verticalHeader().setVisible(False)  # hide the verticalHeader / or not
        self.table5.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table5.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
        self.table5.customContextMenuRequested[QtCore.QPoint].connect(
            lambda pos: self.table_event(pos, self.table5))  # the right click event
        self.table5.hide()

        titles6 = self.db.Get_Col_Name('his_bus')
        self.table6 = QTableWidget(self)  # creat a empty table
        self.table6.setRowCount(9)
        self.table6.setColumnCount(len(titles6))
        self.table6.setHorizontalHeaderLabels(titles6)
        # self.table6.verticalHeader().setVisible(False)  # hide the verticalHeader / or not
        self.table6.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table6.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
        self.table6.customContextMenuRequested[QtCore.QPoint].connect(
            lambda pos: self.table_event(pos, self.table6))  # the right click event
        self.table6.hide()

        self.table_btn_layout = QHBoxLayout()
        self.Form3_layout = QVBoxLayout()
        self.choose_btn_layout = QVBoxLayout()

        self.Choose_Road_btn = QPushButton('道路信息')
        self.Choose_Crossing_btn = QPushButton('路口信息')
        self.Choose_Bus_btn = QPushButton('在途公交车')
        self.Choose_Requset_btn = QPushButton('优先请求')
        self.Choose_Station_btn = QPushButton('公交车站')
        self.Choose_His_bus_btn = QPushButton('历史公交车')

        self.Choose_Road_btn.setFixedSize(120, 30)
        self.Choose_Crossing_btn.setFixedSize(120, 30)
        self.Choose_Bus_btn.setFixedSize(120, 30)
        self.Choose_Requset_btn.setFixedSize(120, 30)
        self.Choose_Station_btn.setFixedSize(120, 30)
        self.Choose_His_bus_btn.setFixedSize(120, 30)

        btn_qssStyle2 = ''' 
                            QPushButton{
                            text-align : center;
                            font-family:Microsoft Yahei;
                            font-size:11pt;
                            background-color : #F5F5F5;
                            border-color: gray;
                            border-width: 2px;
                            border-radius: 10px;
                            padding: 1px;
                            height : 14px;
                            border-style: outset;
                            }
                            QPushButton:pressed{
                            text-align : center;
                            font-family:Microsoft Yahei;
                            font-size:11pt;
                            background-color : #ADD8E6;
                            border-color: gray;
                            border-width: 2px;
                            border-radius: 10px;
                            padding: 1px;
                            height : 14px;
                            border-style: outset;
                            }
                        '''
        self.Choose_Road_btn.setStyleSheet(btn_qssStyle2)
        self.Choose_Crossing_btn.setStyleSheet(btn_qssStyle2)
        self.Choose_Bus_btn.setStyleSheet(btn_qssStyle2)
        self.Choose_Requset_btn.setStyleSheet(btn_qssStyle2)
        self.Choose_Station_btn.setStyleSheet(btn_qssStyle2)
        self.Choose_His_bus_btn.setStyleSheet(btn_qssStyle2)

        self.choose_btn_layout.addWidget(self.Choose_Road_btn)
        self.choose_btn_layout.addWidget(self.Choose_Crossing_btn)
        self.choose_btn_layout.addWidget(self.Choose_Bus_btn)
        self.choose_btn_layout.addWidget(self.Choose_Requset_btn)
        self.choose_btn_layout.addWidget(self.Choose_Station_btn)
        self.choose_btn_layout.addWidget(self.Choose_His_bus_btn)

        self.table_btn_layout.addItem(self.choose_btn_layout)

        self.table_layout = QHBoxLayout()  # 专门用来放表格的layout
        self.table_layout.addWidget(self.table1)
        self.table_layout.addWidget(self.table2)
        self.table_layout.addWidget(self.table3)
        self.table_layout.addWidget(self.table4)
        self.table_layout.addWidget(self.table5)
        self.table_layout.addWidget(self.table6)
        self.table1.show()

        self.table_btn_layout.addItem(self.table_layout)
        self.Form3_layout.addItem(self.btn_layout)
        self.Form3_layout.addItem(self.table_btn_layout)
        self.setLayout(self.Form3_layout)

        self.Choose_Road_btn.clicked.connect(self.Choose_Road_btn_Clicked)
        self.Choose_Crossing_btn.clicked.connect(self.Choose_Crossing_btn_Clicked)
        self.Choose_Bus_btn.clicked.connect(self.Choose_Bus_btn_Clicked)
        self.Choose_Requset_btn.clicked.connect(self.Choose_Requset_btn_Clicked)
        self.Choose_Station_btn.clicked.connect(self.Choose_Station_btn_Clicked)
        self.Choose_His_bus_btn.clicked.connect(self.Choose_His_bus_btn_Clicked)

        self.btnPress1.clicked.connect(self.btnPress1_Clicked)
        self.btnPress2.clicked.connect(self.btnPress2_Clicked)
        self.btnPress3.clicked.connect(self.btnPress3_Clicked)
        self.btnPress4.clicked.connect(self.btnPress4_Clicked)
        self.btnPress5.clicked.connect(self.btnPress5_Clicked)
        self.btnPress6.clicked.connect(self.btnPress6_Clicked)

        # 创建动态更新表格数据对象
        self.backend = Update_table()
        self.backend.signal_write_msg.connect(self.Write_Table)
        self.backend.signal_combox_msg.connect(self.Write_Cob_Table)
        self.backend.signal_update_table_msg.connect(self.Update_Table)
        self.backend.start()

    def btnPress1_Clicked(self):
        """
        button1:Create the database and fill the db with the init data
        创建数据库，并使用初始数据表格（/data/data.xlsx）初始化数据库
        """
        if self.Flag_run_vissim:
            QMessageBox.critical(self, "critical", "仿真正在运行，请先停止仿真", QMessageBox.Yes | QMessageBox.No,
                                 QMessageBox.Yes)
            return
        action = QMessageBox.critical(self, "information", "此项操作将丢失现有数据，是否继续？", QMessageBox.Yes | QMessageBox.No,
                                      QMessageBox.Yes)

        if action == QMessageBox.Yes:
            try:
                self.db.Init_DB()
                self.db.OC_Filling()
                # self.btnPress3_Clicked()  # 表格加载数据
            except:
                QMessageBox.critical(self, "critical", "发生未知错误", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
            else:
                QMessageBox.information(self, "information", "已重置数据库", QMessageBox.Yes | QMessageBox.No,
                                        QMessageBox.Yes)

    def btnPress2_Clicked(self):
        """
        button2:clear all the date in the db
        删除所有数据，重新创建空数据表s
        """
        if self.Flag_run_vissim:
            QMessageBox.critical(self, "critical", "仿真正在运行，请先停止仿真", QMessageBox.Yes | QMessageBox.No,
                                 QMessageBox.Yes)
            return

        A = QMessageBox.critical(self, "critical", "此项操作将清除数据库所有数据，是否继续？", QMessageBox.Yes | QMessageBox.No,
                                 QMessageBox.Yes)
        if A == QMessageBox.Yes:
            try:
                self.db.Init_DB()
                self.btnPress3_Clicked()
            except:
                QMessageBox.critical(self, "critical", "错误", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
            else:
                QMessageBox.information(self, "information", "已完成清空数据库！", QMessageBox.Yes | QMessageBox.No,
                                        QMessageBox.Yes)

    def btnPress3_Clicked(self):
        """
        button3:load data from the db and fill the tabelwidget
        :return:
        """
        pass
        # QMessageBox.information(self, "information", "已完成数据加载！", QMessageBox.Yes | QMessageBox.No,
        #                         QMessageBox.Yes)
        # func = [self.db.Read_Road(), self.db.Read_Crossing(), self.db.Read_Bus(), self.db.Read_Request(),
        #         self.db.Read_Station(),
        #         self.db.Read_His_Bus()]
        # # table = [self.table1, self.table2, self.table3, self.table4, self.table5, self.table6]
        # for k in range(len(func)):
        #     mess = func[k]
        #     # table[k].clearContents()  # 清空表格内原有的内容
        #     self.signal_update_table_msg.emit([k, 'clear', 0])
        #     # table[k].setRowCount(len(mess))  # 设置行数--不包括标题列
        #     self.signal_update_table_msg.emit([k, 'set_col', len(mess)])
        #
        #     if k == 0:
        #         """Road"""
        #         for i in range(len(mess)):
        #             for j in range(len(mess[i])):
        #                 addmess = str(mess[i][j])
        #                 # table[k].setItem(i, j, QTableWidgetItem(addmess))
        #                 self.signal_write_msg.emit([k, i, j, addmess])
        #     elif k == 1:
        #         """Crossing"""
        #         # combox_item = db.GET_all_Road_id()
        #         combox_item = self.db.GET_all_Road_name()
        #         for i in range(len(mess)):
        #             for j in range(1):
        #                 addmess = str(mess[i][j])
        #                 # table[k].setItem(i, j, QTableWidgetItem(addmess))
        #                 self.signal_write_msg.emit([k, i, j, addmess])
        #             for j in range(1, 5):
        #                 comboxColumn = QComboBox()
        #                 comboxColumn.addItems(combox_item)
        #                 index = combox_item.index(mess[i][j])
        #                 comboxColumn.setCurrentIndex(index)
        #                 # table[k].setCellWidget(i, j, comboxColumn)
        #                 self.signal_combox_msg.emit([k, i, j, comboxColumn])
        #             for j in range(5, len(mess[i])):
        #                 addmess = str(mess[i][j])
        #                 # table[k].setItem(i, j, QTableWidgetItem(addmess))
        #                 self.signal_write_msg.emit([k, i, j, addmess])
        #     elif k == 2:
        #         """Bus"""
        #         combox_item_Road = self.db.GET_all_Road_id()
        #         for i in range(len(mess)):
        #             for j in range(1):
        #                 addmess = str(mess[i][j])
        #                 # table[k].setItem(i, j, QTableWidgetItem(addmess))
        #                 self.signal_write_msg.emit([k, i, j, addmess])
        #             for j in range(1, 2):
        #                 comboxColumn = QComboBox()
        #                 comboxColumn.addItems(combox_item_Road)
        #                 index = combox_item_Road.index(mess[i][j])
        #                 comboxColumn.setCurrentIndex(index)
        #                 # table[k].setCellWidget(i, j, comboxColumn)
        #                 self.signal_combox_msg.emit([k, i, j, comboxColumn])
        #             for j in range(2, len(mess[i])):
        #                 addmess = str(mess[i][j])
        #                 # table[k].setItem(i, j, QTableWidgetItem(addmess))
        #                 self.signal_write_msg.emit([k, i, j, addmess])
        #     elif k == 3:
        #         """Request"""
        #         combox_item_Crossing = self.db.GET_all_Crossing_id()
        #         combox_item_Bus = self.db.GET_all_Bus_id()
        #         for i in range(len(mess)):
        #             for j in range(1):
        #                 comboxColumn = QComboBox()
        #                 comboxColumn.addItems(combox_item_Crossing)
        #                 index = combox_item_Crossing.index(mess[i][j])
        #                 comboxColumn.setCurrentIndex(index)
        #                 # table[k].setCellWidget(i, j, comboxColumn)
        #                 self.signal_combox_msg.emit([k, i, j, comboxColumn])
        #             for j in range(1, 2):
        #                 comboxColumn = QComboBox()
        #                 comboxColumn.addItems(combox_item_Bus)
        #                 index = combox_item_Bus.index(mess[i][j])
        #                 comboxColumn.setCurrentIndex(index)
        #                 # table[k].setCellWidget(i, j, comboxColumn)
        #                 self.signal_combox_msg.emit([k, i, j, comboxColumn])
        #             for j in range(2, len(mess[i])):
        #                 addmess = str(mess[i][j])
        #                 # table[k].setItem(i, j, QTableWidgetItem(addmess))
        #                 self.signal_write_msg.emit([k, i, j, addmess])
        #     elif k == 4:
        #         """His_bus"""
        #         for i in range(len(mess)):
        #             for j in range(len(mess[i])):
        #                 addmess = str(mess[i][j])
        #                 # table[k].setItem(i, j, QTableWidgetItem(addmess))
        #                 self.signal_write_msg.emit([k, i, j, addmess])
        #     elif k == 5:
        #         """His_bus"""
        #         for i in range(len(mess)):
        #             for j in range(len(mess[i])):
        #                 addmess = str(mess[i][j])
        #                 # table[k].setItem(i, j, QTableWidgetItem(addmess))
        #                 self.signal_write_msg.emit([k, i, j, addmess])

    def btnPress4_Clicked(self):
        """
        button4:update db with the tabelwidget data
        实现修改数据功能，初始化空数据表，将tabelwidget中的数据存储至数据库
        """
        if self.Flag_run_vissim:
            QMessageBox.critical(self, "critical", "仿真正在运行，请先停止仿真", QMessageBox.Yes | QMessageBox.No,
                                 QMessageBox.Yes)
            return

        try:
            self.db.Init_DB()
        except:
            QMessageBox.critical(self, "critical", "错误", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
            return 0

        try:
            func = [self.db.Read_Road(), self.db.Read_Crossing(), self.db.Read_Bus(), self.db.Read_Request()]
            table = [self.table1, self.table2, self.table3, self.table4]
            for k in range(len(func)):
                mess = []
                cur_table = table[k]
                for i in range(cur_table.rowCount()):
                    row_mess = []
                    for j in range(cur_table.columnCount()):
                        if not cur_table.item(i, j) is None:
                            row_mess.append(cur_table.item(i, j).text())
                        else:
                            row_mess.append(cur_table.cellWidget(i, j).currentText())
                    mess.append(row_mess)
                for item in mess:
                    args = tuple(item)
                    if k == 0:
                        self.db.add_Road(args)
                    elif k == 1:
                        self.db.add_Crossing(args)
                    elif k == 2:
                        self.db.add_Bus(args)
                    else:
                        self.db.add_Request(args)
        except:
            QMessageBox.critical(self, "critical", "发生未知错误！", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
        else:
            QMessageBox.information(self, "information", "成功修改数据！", QMessageBox.Yes | QMessageBox.No,
                                    QMessageBox.Yes)

    def btnPress5_Clicked(self):
        """
        button5: save the tabelwidget data as a .xlsx file
        存储数据
        """
        print(self.Flag_run_vissim)
        if self.Flag_run_vissim:
            QMessageBox.critical(self, "critical", "仿真正在运行，请先停止仿真", QMessageBox.Yes | QMessageBox.No,
                                 QMessageBox.Yes)
            return

        openfile_name = QFileDialog.getSaveFileName(self, '选择文件', filter='.xlsx')
        if openfile_name is None:
            return
        else:
            try:
                filename = openfile_name[0] + openfile_name[1]
                name = ['road', 'crossing', 'bus', 'request']
                func = [self.db.Read_Road(), self.db.Read_Crossing(), self.db.Read_Bus(), self.db.Read_Request()]
                table = [self.table1, self.table2, self.table3, self.table4]
                titles = [self.db.Get_Col_Name('road'), self.db.Get_Col_Name('crossing'), self.db.Get_Col_Name('bus'),
                          self.db.Get_Col_Name('request')]
                for k in range(4):
                    sheet_index = k
                    sheet_name = name[k]
                    data = []
                    data.append(titles[k])
                    cur_table = table[k]
                    for i in range(cur_table.rowCount()):
                        row_mess = []
                        for j in range(cur_table.columnCount()):
                            if not cur_table.item(i, j) is None:
                                row_mess.append(cur_table.item(i, j).text())
                            else:
                                try:
                                    row_mess.append(cur_table.cellWidget(i, j).currentText())
                                except:
                                    QMessageBox.critical(self, "critical", "错误，数据为空", QMessageBox.Yes | QMessageBox.No,
                                                         QMessageBox.Yes)
                                    return
                        data.append(row_mess)
                    XLSX.write_xlsx(filename, sheet_index, sheet_name, data)
            except:
                QMessageBox.critical(self, "critical", "发生未知错误！", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
            else:
                QMessageBox.information(self, "information", "Finish!", QMessageBox.Yes | QMessageBox.No,
                                        QMessageBox.Yes)

    def btnPress6_Clicked(self):
        """
        使用数据文件填充数据库
        """

        if self.Flag_run_vissim:
            QMessageBox.critical(self, "critical", "仿真正在运行，请先停止仿真", QMessageBox.Yes | QMessageBox.No,
                                 QMessageBox.Yes)
            return

        openfile_name = QFileDialog.getOpenFileName(self, '选择文件', filter='.xlsx')
        if openfile_name is None:
            return
        try:
            workBook = xlrd.open_workbook(openfile_name)
        except:
            QMessageBox.critical(self, "critical", "打开文件失败！", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
            return
        else:
            try:
                self.db.Init_DB()
                self.db.Fill_With_File(filename=openfile_name)
            except:
                QMessageBox.critical(self, "critical", "发生未知错误！", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
            else:
                QMessageBox.information(self, "information", "完成！", QMessageBox.Yes | QMessageBox.No,
                                        QMessageBox.Yes)

    def table_event(self, pos, table):
        """
        right click in the tabelwidget; to add or del a row
        ref: https://blog.csdn.net/qq_30992103/article/details/103092298
        """
        pop_menu = QMenu()
        add_new = pop_menu.addAction('添加新一行')
        del_selected = pop_menu.addAction('删除选中行')
        action = pop_menu.exec_(table.mapToGlobal(pos))  # 获取右键菜单中当前被点击的是哪一项

        if action == add_new:
            cur_total_row = table.rowCount()
            cur_row = cur_total_row + 1
            table.setRowCount(cur_row)  # 将当前的总行数加1

        elif action == del_selected:
            selected_items = table.selectedItems()
            if len(selected_items) == 0:  # 说明没有选中任何行
                return
            selected_items = [selected_items[i] for i in range(len(selected_items) - 1, -1, -7)]
            # 将选定行的行号降序排序，只有从索引大的行开始删除，才不会出现错误
            for items in selected_items:
                table.removeRow(table.indexFromItem(items).row())

    def Choose_Road_btn_Clicked(self):
        self.Choose_Road_btn.setDown(True)
        self.Choose_Crossing_btn.setDown(False)
        self.Choose_Bus_btn.setDown(False)
        self.Choose_Requset_btn.setDown(False)
        self.Choose_Station_btn.setDown(False)
        self.Choose_His_bus_btn.setDown(False)
        self.table1.show()
        self.table2.hide()
        self.table3.hide()
        self.table4.hide()
        self.table5.hide()
        self.table6.hide()

    def Choose_Crossing_btn_Clicked(self):
        self.Choose_Road_btn.setDown(False)
        self.Choose_Crossing_btn.setDown(True)
        self.Choose_Bus_btn.setDown(False)
        self.Choose_Requset_btn.setDown(False)
        self.Choose_Station_btn.setDown(False)
        self.Choose_His_bus_btn.setDown(False)
        self.table1.hide()
        self.table2.show()
        self.table3.hide()
        self.table4.hide()
        self.table5.hide()
        self.table6.hide()

    def Choose_Bus_btn_Clicked(self):
        self.Choose_Road_btn.setDown(False)
        self.Choose_Crossing_btn.setDown(False)
        self.Choose_Bus_btn.setDown(True)
        self.Choose_Requset_btn.setDown(False)
        self.Choose_Station_btn.setDown(False)
        self.Choose_His_bus_btn.setDown(False)
        self.table1.hide()
        self.table2.hide()
        self.table3.show()
        self.table4.hide()
        self.table5.hide()
        self.table6.hide()

    def Choose_Requset_btn_Clicked(self):
        self.Choose_Road_btn.setDown(False)
        self.Choose_Crossing_btn.setDown(False)
        self.Choose_Bus_btn.setDown(False)
        self.Choose_Requset_btn.setDown(True)
        self.Choose_Station_btn.setDown(False)
        self.Choose_His_bus_btn.setDown(False)
        self.table1.hide()
        self.table2.hide()
        self.table3.hide()
        self.table4.show()
        self.table5.hide()
        self.table6.hide()

    def Choose_Station_btn_Clicked(self):
        self.Choose_Road_btn.setDown(False)
        self.Choose_Crossing_btn.setDown(False)
        self.Choose_Bus_btn.setDown(False)
        self.Choose_Requset_btn.setDown(False)
        self.Choose_Station_btn.setDown(True)
        self.Choose_His_bus_btn.setDown(False)
        self.table1.hide()
        self.table2.hide()
        self.table3.hide()
        self.table4.hide()
        self.table5.show()
        self.table6.hide()

    def Choose_His_bus_btn_Clicked(self):
        self.Choose_Road_btn.setDown(False)
        self.Choose_Crossing_btn.setDown(False)
        self.Choose_Bus_btn.setDown(False)
        self.Choose_Requset_btn.setDown(False)
        self.Choose_Station_btn.setDown(False)
        self.Choose_His_bus_btn.setDown(True)
        self.table1.hide()
        self.table2.hide()
        self.table3.hide()
        self.table4.hide()
        self.table5.hide()
        self.table6.show()

    def Write_Table(self, msg):
        table = [self.table1, self.table2, self.table3, self.table4, self.table5, self.table6]
        k = msg[0]  # 第几个表格
        i = msg[1]
        j = msg[2]
        add_mess = msg[3]
        table[k].setItem(i, j, QTableWidgetItem(add_mess))

    def Write_Cob_Table(self, msg):
        table = [self.table1, self.table2, self.table3, self.table4, self.table5, self.table6]
        k = msg[0]  # 第几个表格
        i = msg[1]
        j = msg[2]
        combox_item = msg[3]
        combox_mess = msg[4]
        comboxColumn = QComboBox()
        comboxColumn.addItems(combox_item)
        index = combox_item.index(combox_mess)
        comboxColumn.setCurrentIndex(index)
        table[k].setCellWidget(i, j, comboxColumn)

    def Update_Table(self, msg):
        table = [self.table1, self.table2, self.table3, self.table4, self.table5, self.table6]
        k = msg[0]  # 第几个表格
        msg_type = msg[1]
        msg_index = msg[2]
        if msg_type == 'clear':
            table[k].clearContents()  # 清空表格内原有的内容
        elif msg_type == 'set_col':
            table[k].setRowCount(msg_index)  # 设置行数--不包括标题列
